/*
 * Copyright 2006-2023 ahtelit.com, All Rights Reserved.
 */

package com.telit.firecommand.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.telit.firecommand.entity.SysEquipStation;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;


/**
 * SysEquipStationDAO
 *
 * @author yuezhuang
 * @since 2023-03-31
 */
@Repository
public interface SysEquipStationDAO extends BaseMapper<SysEquipStation> {

    @Select("<script> "
            + "select *,"
            + " ((exists(select 1 from sci_meteoric_data where code = sys_equip_station.code and sci_meteoric_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_meteoric_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS') )) or "
            + "  (exists(select 1 from sci_water_quality_data where code = sys_equip_station.code and sci_water_quality_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_water_quality_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS')))) "
            + " as status "
            + "from sys_equip_station  "
            + "where 1 = 1 "

            + "<if test=\"districtCode != null and districtCode != '' and areaRecursion == true \"> "
            + "and district_code like concat(#{districtCode},'%') "
            + "</if> "

            + "<if test=\"districtCode != null and districtCode != '' and areaRecursion == false \"> "
            + "and district_code = #{districtCode} "
            + "</if> "

            + "<if test=\"name != null\"> "
            + "and concat_ws('', name) like concat('%', #{name}, '%') "
            + "</if> "

            + "<if test=\"type != null\"> "
            + "AND type = #{type} "
            + "</if> "

            + "<if test=\"status != null and status == 1\"> "
            + "AND ((exists(select 1 from sci_meteoric_data where code = sys_equip_station.code and sci_meteoric_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_meteoric_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS') )) or "
            + "       (exists(select 1 from sci_water_quality_data where code = sys_equip_station.code and sci_water_quality_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_water_quality_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS')))) "
            + "</if> "

            + "<if test=\"status != null and status == 0\"> "
            + "AND (( not exists(select 1 from sci_meteoric_data where code = sys_equip_station.code and sci_meteoric_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_meteoric_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS') )) and "
            + "       ( not exists(select 1 from sci_water_quality_data where code = sys_equip_station.code and sci_water_quality_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_water_quality_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS')))) "
            + "</if> "

            + "<if test=\"pageSize != null and offset != null\"> "
            + "limit #{pageSize} offset #{offset} "
            + "</if> "

            + "</script>")
    List<SysEquipStation> selectByCondition(@Param("districtCode") String districtCode,
                                            @Param("areaRecursion") boolean areaRecursion,
                                            @Param("type") Integer type, @Param("status") Integer status,
                                            @Param("name") String name, @Param("startTime") String startTime,
                                            @Param("endTime") String endTime, @Param("pageSize") Integer pageSize,
                                            @Param("offset") Integer offset);

    @Select("<script> "
            + "select *,"
            + " ((exists(select 1 from sci_meteoric_data where code = sys_equip_station.code and sci_meteoric_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_meteoric_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS') )) or "
            + "  (exists(select 1 from sci_water_quality_data where code = sys_equip_station.code and sci_water_quality_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_water_quality_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS')))) "
            + " as status "
            + "from sys_equip_station  "
            + "where id = #{id} "
            + "</script>")
    SysEquipStation info(@Param("id") Long id, @Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("<script> "
            + "select count(distinct id) "
            + "from sys_equip_station  "
            + "where 1 = 1 "

            + "<if test=\"districtCode != null and districtCode != '' and areaRecursion == true \"> "
            + "and district_code like concat(#{districtCode},'%') "
            + "</if> "

            + "<if test=\"districtCode != null and districtCode != '' and areaRecursion == false \"> "
            + "and district_code = #{districtCode} "
            + "</if> "

            + "<if test=\"name != null\"> "
            + "and concat_ws('', name) like concat('%', #{name}, '%') "
            + "</if> "

            + "<if test=\"type != null\"> "
            + "AND type = #{type} "
            + "</if> "

            + "<if test=\"status != null and status == 1\"> "
            + "AND ((exists(select 1 from sci_meteoric_data where code = sys_equip_station.code and sci_meteoric_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_meteoric_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS') )) or "
            + "       (exists(select 1 from sci_water_quality_data where code = sys_equip_station.code and sci_water_quality_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_water_quality_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS')))) "
            + "</if> "

            + "<if test=\"status != null and status == 0\"> "
            + "AND (( not exists(select 1 from sci_meteoric_data where code = sys_equip_station.code and sci_meteoric_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_meteoric_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS') )) and "
            + "       ( not exists(select 1 from sci_water_quality_data where code = sys_equip_station.code and sci_water_quality_data.create_time <![CDATA[ >= ]]> to_timestamp(#{startTime},'yyyy-mm-dd hh24:MI:SS') and sci_water_quality_data.create_time <![CDATA[ <= ]]> to_timestamp(#{endTime},'yyyy-mm-dd hh24:MI:SS')))) "
            + "</if> "

            + "</script>")
    long count(@Param("districtCode") String districtCode, @Param("areaRecursion") boolean areaRecursion,
               @Param("type") Integer type, @Param("status") Integer status, @Param("name") String name,
               @Param("startTime") String startTime, @Param("endTime") String endTime);

}
